-- Start of DDL Script for Package PEMEX2.PKG_SOLICITUD_TURNAR
-- Generated 03/04/2012 18:26:17 from PEMEX2@XE

CREATE OR REPLACE 
PACKAGE pkg_solicitud_turnar
  IS

  FUNCTION getTurnados(pCve_solicitud_obra number) RETURN sys_refcursor;
  PROCEDURE Registrar
     (  pCVE_SOLICITUD_TURNAR        IN VARCHAR2,
        pCVE_USUARIO                 IN VARCHAR2,
        pCOMENTARIOS                 IN VARCHAR2,
        pCVE_TRABAJO_REALIZAR        IN VARCHAR2,
        pCVE_USUARIO_TURNO           IN VARCHAR2
     );
  PROCEDURE Eliminar
     (
        pCVE           IN varchar2
     );

END; -- Package spec
/


CREATE OR REPLACE 
PACKAGE BODY pkg_solicitud_turnar
IS

   PROCEDURE Registrar
     (  pCVE_SOLICITUD_TURNAR        IN VARCHAR2,
        pCVE_USUARIO                 IN VARCHAR2,
        pCOMENTARIOS                 IN VARCHAR2,
        pCVE_TRABAJO_REALIZAR        IN VARCHAR2,
        pCVE_USUARIO_TURNO           IN VARCHAR2
     )IS
     clave          number;
     total_sup      number;
     total_jefe_sector number;
     exc_persona    exception;
     exc_jefeSector    exception;
     rol_usuario    number;
     encontrado NUMBER;
   BEGIN
        -- Validar que solo un supervisor y jefe de sector por cada obra
        select  count(*) into total_sup from solicitud_turnar a, cat_usuario b
        where
            a.cve_usuario           = b.cve_usuario and
            b.cve_rol               = 2 and
            a.CVE_TRABAJO_REALIZAR  = pCVE_TRABAJO_REALIZAR;

        select  count(*) into total_jefe_sector from solicitud_turnar a, cat_usuario b
        where
            a.cve_usuario           = b.cve_usuario and
            b.cve_rol               = 1 and
            a.CVE_TRABAJO_REALIZAR  = pCVE_TRABAJO_REALIZAR;
            
        select cve_rol into rol_usuario from cat_usuario where cve_usuario = pCVE_USUARIO;
        if (pCVE_SOLICITUD_TURNAR is null or length(pCVE_SOLICITUD_TURNAR) =0 ) then
             -- Validar que la persona no ya este asignada a la obra 
            SELECT COUNT(*) INTO encontrado FROM SOLICITUD_TURNAR WHERE cve_usuario = pcve_usuario AND 
            CVE_TRABAJO_REALIZAR = pCVE_TRABAJO_REALIZAR ;
            
            IF (encontrado = 0)THEN                
                if (total_sup > 0 and rol_usuario=2 ) then -- Avisar que  no es posible
                    raise exc_persona;
                elsif (total_jefe_sector > 0 and rol_usuario=1) then-- Solo un  jefe de sector por cada obra
                    raise exc_jefeSector;
                end if;
                
                select SEQ_SOLICITUD_TURNAR.NEXTVAL into clave from dual;
    
                INSERT INTO SOLICITUD_TURNAR (
                   CVE_SOLICITUD_TURNAR, CVE_USUARIO, ES_ACTUAL,
                   COMENTARIOS, CVE_TRABAJO_REALIZAR,CVE_USUARIO_TURNO)
                VALUES (
                    clave, pCVE_USUARIO, null,
                    pCOMENTARIOS, pCVE_TRABAJO_REALIZAR,pCVE_USUARIO_TURNO);                    
            END IF; -- ENCONTRADO
            else
            UPDATE SOLICITUD_TURNAR
            SET    --CVE_SOLICITUD_TURNAR = pCVE_SOLICITUD_TURNAR,
                   CVE_USUARIO          = pCVE_USUARIO,
                   --ES_ACTUAL            = pES_ACTUAL,
                   COMENTARIOS          = pCOMENTARIOS
                   --CVE_TRABAJO_REALIZAR = pCVE_TRABAJO_REALIZAR
            WHERE  CVE_SOLICITUD_TURNAR = pCVE_SOLICITUD_TURNAR;
        end if;
          
  EXCEPTION
    WHEN exc_persona THEN
        raise_application_error(-20991, 'Esta Obra solo puede tener un Supervisor asignado');
    WHEN exc_jefeSector THEN
        raise_application_error(-20993, 'Esta Obra solo puede tener un Jefe de Sector asignado');
    WHEN others then
        raise_application_error(-20999,'Error No-'||to_char(sqlcode)||':'||sqlerrm);
        
   END;
   
   
 FUNCTION getTurnados(pCve_solicitud_obra number) RETURN sys_refcursor
    IS
    resultado sys_refcursor;
    BEGIN
    OPEN resultado FOR
        SELECT a.cve_solicitud_turnar, a.cve_usuario, a.es_actual,
               a.comentarios, a.cve_trabajo_realizar,  to_char(a.fecha_registro,'dd/mm/yyyy Hh:mm') fecha_registro,
               a.fecha_ultimo_cambio, a.cve_usuario_mtto, B.nombre_usuario TURNADO_A, C.nombre_usuario QUIEN_TURNO,
               B.nombre||' '||b.apellido_p||' '||c.apellido_m nombre_turnado,
               d.descripcion ROL,
               b.cve_rol
        FROM solicitud_turnar a, CAT_USUARIO B, CAT_USUARIO C, cat_rol d
        WHERE
                A.cve_usuario           = B.cve_usuario
            AND A.cve_usuario_turno     = C.cve_usuario(+)
            AND CVE_TRABAJO_REALIZAR    = pCve_solicitud_obra
            and b.cve_rol               = d.cve_rol
        order by A.FECHA_REGISTRO desc;

    return resultado;
    END;
    
   PROCEDURE Eliminar
     (
        pCVE           IN varchar2
     )IS

     clave number;
   BEGIN

        DELETE solicitud_turnar
        WHERE  CVE_SOLICITUD_TURNAR    = pCVE;

   END;
   

    
       
END;
/


-- End of DDL Script for Package PEMEX2.PKG_SOLICITUD_TURNAR
